package org.ccay.excel.importer.internal.impl;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.WeakHashMap;

import javax.inject.Named;
import javax.xml.stream.XMLStreamException;

import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.core.io.Resource;

import org.ccay.core.exception.ApplicationException;
import org.ccay.core.log.ILogger;
import org.ccay.core.log.CcayLoggerFactory;
import org.ccay.core.util.StreamUtil;
import org.ccay.core.util.StringUtil;
import org.ccay.excel.exception.ExcelApplicationException;
import org.ccay.excel.exception.ExcelExportConstants;
import org.ccay.excel.importer.service.impl.internal.ExcelMetaParser;
import org.ccay.excel.importer.validate.impl.ExcelValidatorManager;
import org.ccay.excel.meta.ColumnMeta;
import org.ccay.excel.meta.ExcelMeta;
import org.ccay.excel.meta.IExcelMetaManager;
import org.ccay.excel.meta.SheetMeta;
import org.ccay.excel.util.impl.ExcelUtil;

/**
 * Excel元数据管理器。
 * 
 * @author chaowangbang
 * @since Mar 16, 2012
 */
@Named
public class ExcelMetaManager implements IExcelMetaManager, InitializingBean {

	/**
	 * ExcelMeta的缓存. 注意多线程的同步
	 */
	private Map<String, ExcelMeta> cachedExcelMetas = new WeakHashMap<String, ExcelMeta>();

	/**
	 * ExcelMeta解析器
	 */
	private ExcelMetaParser exelMetaParser = new ExcelMetaParser();

	// 异常编码
	private static final String GENERATETEMPLATE_EXCEPTION = "ccay.excel.00080002";

	private static final int TITLEINDEX = 1;
	private static final short WIDTH = 500;

	/** 日志类 */
	private final static ILogger logger = CcayLoggerFactory.getLogger(ExcelMetaManager.class);

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.ccay.excel.meta.IExcelMetaManager#getExcelMeta(java.lang
	 * .String)
	 */
	public ExcelMeta getExcelMeta(String excelType) throws ApplicationException {
		// 若缓存中存在元数据则从缓存中读取
		if (cachedExcelMetas.containsKey(excelType)) {
			return cachedExcelMetas.get(excelType);
		}

		String resPath = "config/excel/" + excelType + ".excelImport.xml";
		InputStream in = null;
		try {
			// 获得流
			in = ExcelMetaManager.class.getClassLoader().getResourceAsStream(resPath);
			ExcelMeta excelMeta = getExcelMetaByStream(in);
			if (excelMeta != null && !cachedExcelMetas.containsKey(excelType)) {
				synchronized (cachedExcelMetas) {
					if (!cachedExcelMetas.containsKey(excelType)) {
						cachedExcelMetas.put(excelType, excelMeta);
					}
				}
			}
			return excelMeta;
		} catch (XMLStreamException e) {
			// 解析失败抛出异常
			throw new ExcelApplicationException(GENERATETEMPLATE_EXCEPTION, e);
		} finally {
			StreamUtil.closeStreams(in);
		}

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.ccay.excel.meta.IExcelMetaManager#getExcelMeta(org.
	 * springframework.core.io.Resource)
	 */
	public ExcelMeta getExcelMeta(Resource resource)
			throws ApplicationException {

		// 通过对XML文件解析，产生Excel元数据
		ExcelMeta excelMeta = null;
		InputStream in = null;
		try {
			// 根据输入流解析出Excel元数据
			in = resource.getInputStream();
			excelMeta = getExcelMetaByStream(in);
			cachedExcelMetas.put(excelMeta.getType(), excelMeta);
		} catch (XMLStreamException e) {
			// 解析失败抛出异常
			throw new ExcelApplicationException(GENERATETEMPLATE_EXCEPTION, e);
		} catch (IOException e) {
			// 创建流失败抛出异常
			logger.error("create InputStream failed", e);
			throw new ExcelApplicationException(GENERATETEMPLATE_EXCEPTION, e);
		} finally {
			StreamUtil.closeStreams(in);
		}

		return excelMeta;
	}

	/**
	 * 根据输入流解析元数据
	 * 
	 * @param is
	 * @return
	 * @throws ApplicationException
	 * @throws XMLStreamException
	 */
	private ExcelMeta getExcelMetaByStream(InputStream is)
			throws XMLStreamException, ApplicationException {
		ExcelMeta excelMeta = exelMetaParser.parse(is);
		if (excelMeta == null) {
			throw new ExcelApplicationException(GENERATETEMPLATE_EXCEPTION);
		}
		return excelMeta;
	}

 

	private static Map<String, CellStyle> createStyles(Workbook wb) {
		Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
		styles.put("title", createTitleStyle(wb));
		styles.put(ExcelExportConstants.EXCEL_EXPORT_INT, createFormattedCellStyle(wb, "###0")); // int
		styles.put(ExcelExportConstants.EXCEL_EXPORT_DOUBLE, createFormattedCellStyle(wb, "###0.0###")); // double
		styles.put(ExcelExportConstants.EXCEL_EXPORT_DATE, createFormattedCellStyle(wb, "yyyy-MM-dd")); // date
		styles.put(ExcelExportConstants.EXCEL_EXPORT_TIMESTSMP, createFormattedCellStyle(wb, "yyyy-MM-dd HH:mm:ss")); // timestamp
		styles.put(ExcelExportConstants.EXCEL_EXPORT_STRING, createFormattedCellStyle(wb, "@")); // string
		styles.put("default", createDefaultCellStyle(wb));
		return styles;
	}
	
	private static CellStyle createTitleStyle(Workbook wb) {
		CellStyle style = createDefaultCellStyle(wb);
		// 设置单元格字体
		Font headerFont = wb.createFont();
		headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		headerFont.setFontName("arial,verdana,tahoma");
		style.setFont(headerFont);
		
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
//		style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
		style.setFillForegroundColor(ExcelUtil.getForegroundColorIndex(IndexedColors.LIGHT_YELLOW));
		style.setWrapText(false);
		return style;
	}
	
	
	private static CellStyle createFormattedCellStyle(Workbook wb, String format){
		CellStyle style = createDefaultCellStyle(wb);
		style.setDataFormat(wb.createDataFormat().getFormat(format));
		return style;
	}
	
	
	private static CellStyle createDefaultCellStyle(Workbook wb) {
		CellStyle style;
		style = wb.createCellStyle();
		// 设置单元格字体
		Font headerFont = wb.createFont();
		headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		headerFont.setFontName("arial,verdana,tahoma");
		style.setFont(headerFont);
		// 设置单元格的样式
		style.setWrapText(true);
		return style;
	}
	
	


	/**
	 * 为ExcelVaidatorManager设置Excel元数据管理器。
	 * 
	 * @author chaowangbang
	 * @since Mar 22, 2012
	 * @throws ApplicationException
	 */
	public void afterPropertiesSet() throws ApplicationException {
		ExcelValidatorManager.setExcelMetaManager(this);
	}

	/* (non-Javadoc)
	 * @see org.ccay.excel.meta.IExcelMetaManager#generateExcelTemplate(java.lang.String, java.io.OutputStream)
	 */
	public void generateExcelTemplate(String excelType, OutputStream output) throws ApplicationException {
		// 获取Excel元数据
		ExcelMeta excelMeta = getExcelMeta(excelType);
		generateExcelTemplate(excelMeta, output);
	}
	
	/**
	 * 
	 */
	public void generateExcelTemplate(ExcelMeta excelMeta, OutputStream output)
			throws ApplicationException {
				// 创建Excel文件
				Workbook wb = new XSSFWorkbook();
				// 创建Excel表格样式
				Map<String, CellStyle> styles = createStyles(wb);
				// 遍历Excel元数据获得Sheet元数据
				for (SheetMeta sheetMeta : excelMeta.getSheetList()) {
					// 获得sheet的名称
					String sheetName = sheetMeta.getDisplayName();
					//如果显示名称和name相同  那么需要截取sheet块的名称
					if(sheetName.equals(sheetMeta.getSheetName())){
						sheetName = sheetMeta.getPartSheetName();
					}
					Sheet sheet = wb.getSheet(sheetName);
					// 创建Sheet
					if( null  == sheet){
//						sheet = wb.createSheet(sheetName);
						sheet = ExcelUtil.creatSheet(wb, sheetName);
					}
					// 创建表头的标题列
					Row row = sheet.createRow(sheetMeta.getTitleRowIndex() - TITLEINDEX);
					// 冻结首行
					sheet.createFreezePane(0, sheetMeta.getTitleRowIndex(), 0, TITLEINDEX);
					// 创建HSSFPatriarch对象,HSSFPatriarch是所有注释的容器.
					Drawing patr = sheet.createDrawingPatriarch();
					ClientAnchor anchor = new XSSFClientAnchor();
					// 遍历Sheet元数据获得Column元数据
					List<ColumnMeta> columnMetas = sheetMeta.getColumns();
					// 获得标题列单元格的样式
					CellStyle titleCellStyle = styles.get("title");
					
					int columnSize = columnMetas.size();
					
					// 获取各列标题的字符长度
					int[] titleLengthArray = new int[columnSize];
					for (int column = 0; column < columnSize; column++) {
						ColumnMeta columnMeta = columnMetas.get(column);
						String title = columnMeta.getDisplayName();
						titleLengthArray[column] = StringUtil.getUTF8Length(title);
					}
					
					for (int column = 0; column < columnSize; column++) {
						ColumnMeta columnMeta = columnMetas.get(column);
						// 根据导入配置文件中的type取出对应支持的单元格类型
						String colType = columnMeta.getType();
						String celType = ExcelExportConstants.getExcelExportStupportMap().get(StringUtil.toLower(colType));
						// 设置column列的默认数据格式，写在前面才不会覆盖标题的数据格式
						CellStyle columnStyle =  styles.get(celType);
						if(columnStyle == null){
							columnStyle = styles.get("default");
						}
						sheet.setDefaultColumnStyle(column, columnStyle);
						
						// 创建第column列的表头数据
						Cell cell = row.createCell(column);
						// 获取标题名称,并根据语种类型返回对应标题名称
						String title = columnMeta.getDisplayName();
						// 根据title长度调整列宽
						int titleWidth = titleLengthArray[column];//StringUtil.getUTF8Length(title);
						cell.setCellValue(title);
						sheet.setColumnWidth((short) column, (short) WIDTH * titleWidth);
						
						// 设置column列的标题数据格式
						cell.setCellStyle(titleCellStyle);
						String annotation = columnMeta.getAnnotation();
						if (!StringUtil.isNullOrEmpty(annotation)) {
							int annotationLength = annotation.length();
							// 显示批注的宽度所占单元格数目
							int useColCount = getCommentUseColCount(column, annotationLength, titleLengthArray);
							anchor.setCol1(cell.getColumnIndex());
							anchor.setCol2(cell.getColumnIndex() + useColCount);
							anchor.setRow1(cell.getRowIndex());
							anchor.setRow2(cell.getRowIndex() + 1);
							anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE);
							Comment comment = patr.createCellComment(anchor);
							comment.setString(new XSSFRichTextString(annotation));
							// 设置批注
							cell.setCellComment(comment);
						}
					}
				}

				try {
					wb.write(output);
				} catch (IOException e) {
					// 创建流失败抛出异常
					logger.error("wirte OutputStream failed", e);
					throw new ExcelApplicationException(GENERATETEMPLATE_EXCEPTION, e);
				}
	}
	
	/*
	 * 计算显示批注的宽度所占单元格数目
	 */
	private int getCommentUseColCount(int currentColNumber, int commentLength, int[] titleLength){
		int useCount = 0;
		int useLength = 0;
		int colunmSize = titleLength.length;
		if (colunmSize == 0) {
			return 1;
		}
		while(useLength < commentLength){
			useLength = useLength + titleLength[(currentColNumber + useCount) % colunmSize];
			useCount++;
		}
		return useCount;
	}
}
